package com.hele.sts.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hele.sts.pub.StringUtil;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hele.sts.pub.ConnectDB;
import com.hele.sts.pub.SpringContextHolder;

public class QEkUserDao
{
  static Logger logger = LoggerFactory.getLogger(QEkUserDao.class);
  public static final String TABLE = "da_q_ek_user";
  public static final String TABLE1 = "da_q_user";
  
  public static boolean insert(QEkUser bean)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    String sql = null;
    ConnectDB connsyb = null;
    String fullname = null;
    String beanUserId = bean.getUserId().toString();
    if ((beanUserId.length() > 6) && (beanUserId.substring(0, 6).equals("999999"))) {
      fullname = bean.getFullname();
    } else {
      fullname = loadFullName(bean.getUserId());
    }
    sql = "INSERT INTO da_q_ek_user(userid,username,unit,department,certificate,telephone,physical_address,ip_address,discount,blance,full_name,spqx,gxdw) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
    logger.debug(sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      int i = 1;
      pstmt.setString(i++, bean.getUserId().trim());
      pstmt.setString(i++, bean.getUserName());
      pstmt.setString(i++, bean.getUnit());
      pstmt.setString(i++, bean.getDepartment());
      pstmt.setString(i++, bean.getCertificate());
      pstmt.setString(i++, bean.getTelephone());
      pstmt.setString(i++, bean.getPhysicalAddress());
      pstmt.setString(i++, bean.getIpAddress());
      pstmt.setDouble(i++, bean.getDiscount());
      pstmt.setDouble(i++, bean.getBlance());
      pstmt.setString(i++, fullname);
      //xuning add 20190329
      pstmt.setString(i++, bean.getSpqx());
      pstmt.setString(i++, bean.getGxdw());
      //xuning add 20150113
      //pstmt.setString(i++, bean.getDjId());
      pstmt.execute();
      return true;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    
    return false;
  }
  
  public static boolean delete(String userId)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "DELETE FROM da_q_ek_user WHERE trim(userid)= '" + 
      userId.trim() + "' ";
    logger.debug(sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      pstmt.execute();
      return true;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    
    return false;
  }
  
  public static boolean update(QEkUser bean)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "update da_q_ek_user set username=?,unit=?,department=?,certificate=?,telephone=?,physical_address=?,ip_address=?,discount=?,blance=?";
    sql = sql + 
      " where trim(userid)=?";
    logger.debug(sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      int i = 1;
      pstmt.setString(i++, bean.getUserName());
      pstmt.setString(i++, bean.getUnit());
      pstmt.setString(i++, bean.getDepartment());
      pstmt.setString(i++, bean.getCertificate());
      pstmt.setString(i++, bean.getTelephone());
      pstmt.setString(i++, bean.getPhysicalAddress());
      pstmt.setString(i++, bean.getIpAddress());
      pstmt.setDouble(i++, bean.getDiscount());
      pstmt.setDouble(i++, bean.getBlance());
      pstmt.setString(i++, bean.getUserId().trim());
      pstmt.execute();
      return true;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    
    return false;
  }
  
  public static QEkUser load(String userId)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    logger.debug(userId);
    sql = "SELECT ek.*,q.username as q_username,q.yhlx,q.status FROM da_q_ek_user ek,da_q_user q WHERE ek.userid = q.userid and trim(ek.userid)='" + userId.trim() + "' ";
    
    logger.debug(sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      
      rs = pstmt.executeQuery();
      if (rs.next()) {
        return rsToBean(rs);
      }
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
      DbClose.freeResultSet(rs);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    DbClose.freeResultSet(rs);
    
    return null;
  }
  
  public static boolean hasUsed(String userId)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    boolean hasUsed = false;
    logger.debug(userId.trim());
    sql = "SELECT * FROM da_q_ek_user WHERE trim(userid) = ? ";
    logger.debug(sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, userId.trim());
      rs = pstmt.executeQuery();
      if (rs.next()) {
        hasUsed = true;
      }
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    DbClose.freeResultSet(rs);
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    DbClose.freeResultSet(rs);
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    DbClose.freeResultSet(rs);
    return hasUsed;
  }
  
  public static List load(String userName, String unit, String certificate, int page, int pageSize)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "SELECT distinct trim(userid) as userid,username,unit,department,certificate,telephone,physical_address,ip_address,discount,blance,yhlx,status  FROM (SELECT rownum r, A.Userid,A.UNIT,A.DEPARTMENT,A.CERTIFICATE,A.TELEPHONE,A.PHYSICAL_ADDRESS,A.IP_ADDRESS,A.DISCOUNT,A.BLANCE,A.FULL_NAME,B.yhlx,B.status,B.Username FROM da_q_ek_user A,da_q_user B WHERE B.USERID(+)=A.USERID)";
    if ((userName != null) && (userName.length() > 0)) {
      sql = 
        sql + " AND userName LIKE ? ";
    }
    if ((unit != null) && (unit.length() > 0)) {
      sql = 
        sql + " AND unit LIKE ? ";
    }
    if ((certificate != null) && (certificate.length() > 0)) {
      sql = 
        sql + " AND certificate LIKE ? ";
    }
    if (pageSize > 1) {
      sql = 
        sql + " AND rownum <=? * ?)t WHERE t.r > (? - 1) * ? ";
    }
    logger.debug(sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      int i = 1;
      if ((userName != null) && (userName.length() > 0)) {
        pstmt.setString(i++, 
          "%" + userName + "%");
      }
      if ((unit != null) && (unit.length() > 0)) {
        pstmt.setString(i++, 
          "%" + unit + "%");
      }
      if ((certificate != null) && (certificate.length() > 0)) {
        pstmt.setString(i++, 
          "%" + certificate + "%");
      }
      if (pageSize > 1)
      {
        pstmt.setInt(i++, page);
        pstmt.setInt(i++, pageSize);
        pstmt.setInt(i++, page);
        pstmt.setInt(i++, pageSize);
      }
      rs = pstmt.executeQuery();
      List list = new ArrayList();
      while (rs.next()) {
        list.add(rsToBean1(rs));
      }
      List list1 = list;
      List localList1 = list1;
      return localList1;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
      DbClose.freeResultSet(rs);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    DbClose.freeResultSet(rs);
    
    return null;
  }
  
  public static int count(String userName, String unit, String certificate)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "SELECT count(distinct A.userid) FROM da_q_ek_user A,da_q_user B WHERE B.USERID(+)=A.USERID ";
    if ((userName != null) && (userName.length() > 0)) {
      sql = 
        sql + " AND username LIKE ? ";
    }
    if ((unit != null) && (unit.length() > 0)) {
      sql = 
        sql + " AND unit LIKE ? ";
    }
    if ((certificate != null) && (certificate.length() > 0)) {
      sql = 
        sql + " AND certificate LIKE ? ";
    }
    logger.debug(sql);
    int j = 0;
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      int i = 1;
      if ((userName != null) && (userName.length() > 0)) {
        pstmt.setString(i++, 
          "%" + userName + "%");
      }
      if ((unit != null) && (unit.length() > 0)) {
        pstmt.setString(i++, 
          "%" + unit + "%");
      }
      if ((certificate != null) && (certificate.length() > 0)) {
        pstmt.setString(i++, 
          "%" + certificate + "%");
      }
      rs = pstmt.executeQuery();
      if (!rs.next()) {
        j = rs.getInt(1);
      }
      i = j;
      return i;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
      DbClose.freeResultSet(rs);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    DbClose.freeResultSet(rs);
    
    return 0;
  }
  
  static QEkUser rsToBean(ResultSet rs)
    throws SQLException
  {
    System.out.println(rs.getBigDecimal("blance"));
    QEkUser bean = new QEkUser();
    bean.setBlance(rs.getBigDecimal("blance")==null?0:rs.getBigDecimal("blance").doubleValue());
    bean.setCertificate(rs.getString("certificate"));
    bean.setDepartment(rs.getString("department"));
    bean.setDiscount(rs.getBigDecimal("discount")==null?0:rs.getBigDecimal("discount").doubleValue());
    bean.setIpAddress(rs.getString("ip_address"));
    bean.setPhysicalAddress(rs.getString("physical_address"));
    bean.setTelephone(rs.getString("telephone"));
    bean.setUnit(rs.getString("unit")==null?null:rs.getString("unit").trim());
    bean.setUserId(rs.getString("userid").trim());
    bean.setUserName(rs.getString("username"));
    bean.setEkUserName(rs.getString("q_username"));
    bean.setYhlx(rs.getString("yhlx"));
    //xuning add 20150113
    //bean.setDjId(rs.getString("djid"));
    bean.setGxdw(rs.getString("gxdw")==null?null:rs.getString("gxdw").trim());
    bean.setSpqx(rs.getString("spqx")==null?null:rs.getString("spqx").trim());
    return bean;
  }
  
  static QEkUser rsToBean1(ResultSet rs)
    throws SQLException
  {
    QEkUser bean = new QEkUser();
    bean.setBlance(rs.getBigDecimal("blance").doubleValue());
    bean.setCertificate(rs.getString("certificate"));
    bean.setDepartment(rs.getString("department"));
    bean.setDiscount(rs.getBigDecimal("discount").doubleValue());
    bean.setIpAddress(rs.getString("ip_address"));
    bean.setPhysicalAddress(rs.getString("physical_address"));
    bean.setTelephone(rs.getString("telephone"));
    bean.setUnit(rs.getString("unit"));
    bean.setUserId(rs.getString("userid").trim());
    bean.setUserName(rs.getString("username"));
    System.out.println(rs.getString("username"));
    bean.setYhlx(rs.getString("yhlx"));
    bean.setStatus(rs.getString("status"));
    return bean;
  }
  
  public static ResultSet loadFullByUserId(String userId)
    throws SQLException
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    String sql = null;
    ConnectDB connsyb = null;
    
    sql = "select trim(userid) as userid,username from da_q_user where trim(userid)=?";
    
    connsyb = SpringContextHolder.getBean(ConnectDB.class);
    conn = connsyb.getConnection();
    pstmt = conn.prepareStatement(sql);
    pstmt.setString(1, userId.trim());
    ResultSet rs = pstmt.executeQuery();
    return rs;
  }
  
  public static String getUseridByUsername(String username)
  {
    if(StringUtils.isBlank(username)){
      return null;
    }
    Connection conn = null;
    PreparedStatement pstmt = null;
    String sql = null;
    ConnectDB connsyb = null;
    String userid = null;
    sql = "select trim(userid) as userid from da_q_user where username=?";
    connsyb = SpringContextHolder.getBean(ConnectDB.class);
    ResultSet rs = null;
    try {
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, username);
      rs = pstmt.executeQuery();
      if(rs.next()){
        userid = rs.getString("userid").trim();
      }else{
        userid="";
      }

      return userid;
    } catch (SQLException e) {
      logger.error("getUseridByUsername error",e);
    }

    return null;
  }
  
  public static ResultSet getInfo(String userid)
    throws SQLException
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    String sql = null;
    ConnectDB connsyb = null;
    String username = null;
    sql = "select * from da_q_ek_user where trim(userid)=?";
    connsyb = SpringContextHolder.getBean(ConnectDB.class);
    conn = connsyb.getConnection();
    pstmt = conn.prepareStatement(sql);
    pstmt.setString(1, userid.trim());
    ResultSet rs = pstmt.executeQuery();
    
    return rs;
  }
  
  public static ResultSet loadInfo(String userId)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    logger.debug(userId.trim());
    sql = "SELECT ek.*,q.username as q_username FROM da_q_ek_user ek,da_q_user q WHERE ek.userid = q.userid and trim(ek.userid)='" + userId.trim() + "' ";
    
    logger.debug(sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      
      rs = pstmt.executeQuery();
      if (rs.next()) {
        return rs;
      }
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
      DbClose.freeResultSet(rs);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    DbClose.freeResultSet(rs);
    
    return null;
  }
  
  public static String loadFullName(String userid)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    String fullname = null;
    sql = "select full_name from xt_user where trim(user_id)='" + userid.trim() + "' ";
    
    logger.debug(sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      if (rs.next())
      {
        fullname = rs.getString("full_name");
        return fullname;
      }
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
      DbClose.freeResultSet(rs);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    DbClose.freeResultSet(rs);
    
    return null;
  }
}
